﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using TrainingWorkshop.Model;


namespace TrainingWorkshop.DAL
{
    /// <summary>
    /// Author: Sissi Zeng
    /// Date: 2012-08-08 21:50
    /// Read from CourseList which is a View in Database
    /// </summary>
    public class CourseListView
    {
        
        
        /// <summary>
        /// Construtor
        /// </summary>
        public CourseListView() {
            
        }

        /// <summary>
        /// select correct records in CourseList View
        /// </summary>
        /// <param name="sp">storage procedure</param>
        /// <returns>ResultSet in DataSet</returns>
        public  DataSet SearchCourse(Condition condition)
        {
            DataSet ds = new DataSet();
            string CmdStr = "select * from CourseList where 1=1";
            
            /*
                judge condition
             */

            if (condition.CourseStatus >= 0)
            {
                CmdStr += " and CourseStatus=@CourseStatus";
            }
            if (condition.TeacherName.Length > 0)
            {
                CmdStr += " and TeacherName=@TeacherName";
            }
            if (condition.LevelName.Length > 0)
            {
                CmdStr += " and LevelName=@LevelName";
            }
            if (condition.CourseName.Length > 0)
            {
                CmdStr += " and CourseName like @CourseName";

            }
            


            using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionStringProjectb))
            {
                conn.Open();

                SqlDataAdapter da = new SqlDataAdapter(CmdStr, conn);

                da.SelectCommand.Parameters.AddWithValue("@CourseName", condition.CourseName);
                da.SelectCommand.Parameters.AddWithValue("@CourseStatus", condition.CourseStatus);
                da.SelectCommand.Parameters.AddWithValue("@TeacherName", condition.TeacherName);
                da.SelectCommand.Parameters.AddWithValue("@LevelName", condition.LevelName);
                
                da.Fill(ds, "searchList");
            }
                return ds;
           
              
        }
    }
}
